
Option Strict On

Imports System.Data.SqlClient

' This class provides the database access for a front end. It passes
'   a DataSet containing Supplier and Product information to the front end.
Public Class DataAccess

    ' Initialize constants for connecting to the database
    ' and displaying a connection error to the user.
    Protected Const CONNECTION_ERROR_MSG As String = _
        "To run this sample, you must have SQL " & _
        "or MSDE with the Northwind database installed.  For " & _
        "instructions on installing MSDE, view the ReadMe file."

    Protected Const MSDE_CONNECTION_STRING As String = _
        "Server=(local)\NetSDK;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"

    Protected Const SQL_CONNECTION_STRING As String = _
        "Server=localhost;" & _
        "DataBase=northwind;" & _
        "Integrated Security=SSPI"

    Protected DidPreviouslyConnect As Boolean = False
    ' Create two data adapters--one for each table.
    Private sdaSuppliers As SqlDataAdapter
    Private sdaProducts As SqlDataAdapter

    Protected strConn As String = SQL_CONNECTION_STRING

    ' Declare several events that can be used to provide information to
    '   the front end.
    Public Event ConnectionStatusChange(ByVal status As String)
    Public Event ConnectionFailure(ByVal reason As String)
    Public Event ConnectionCompleted(ByVal success As Boolean)

    ' Create the DataSet used in this sample. It contains two tables consisting of 
    ' Supplier and Product data.
    Public Function CreateDataSet() As DataSet

        Dim dsSupplierProducts As DataSet

        ' Raise a status event saying that the user is attempting to connect.
        ' This only needs to be done the very first time a connection is
        ' attempted.  After we've determined that MSDE or SQL Server is
        ' installed, this message no longer needs to be displayed.
        If Not DidPreviouslyConnect Then
            RaiseEvent ConnectionStatusChange("Connecting to SQL Server")
        End If

        ' Attempt to connect to the local SQL server instance, and a local
        ' MSDE installation (with Northwind).  
        Dim IsConnecting As Boolean = True
        While IsConnecting
            Try
                ' The SqlConnection class allows you to communicate with SQL Server.
                ' The constructor accepts a connection string as an argument.  This
                ' connection string uses Integrated Security, which means that you 
                ' must have a login in SQL Server, or be part of the Administrators
                ' group for this to work.
                Dim scnnNW As New SqlConnection(strConn)

                Dim strSQL As String = _
                    "SELECT * FROM Suppliers"

                ' A SqlCommand object is used to execute the SQL commands.
                Dim scmd As New SqlCommand(strSQL, scnnNW)

                ' A SqlDataAdapter uses the SqlCommand object to fill a DataSet.
                sdaSuppliers = New SqlDataAdapter(scmd)

                ' A SqlCommandBuilder automatically generates the SQL commands needed
                ' to update the database later.
                Dim scb As New SqlCommandBuilder(sdaSuppliers)


                ' Create a new DataSet and fill its first DataTable.
                dsSupplierProducts = New DataSet()
                Me.sdaSuppliers.Fill(dsSupplierProducts, "Supplier")

                ' Create a new SqlDataAdapter, this time simply passing in the
                ' SQL Select Statement
                sdaProducts = New SqlDataAdapter("SELECT * FROM Products", scnnNW)

                ' The commands generated by the SqlCommandBuilder are based on the 
                ' currently set CommandText of the SqlCommand object. As this will
                Dim scbProducts As New SqlCommandBuilder(sdaProducts)


                ' Fill the second table in the DataSet.
                sdaProducts.Fill(dsSupplierProducts, "Product")

                ' OPTIONAL: To see a different kind of Master-Details interface,
                ' in which both the master and details data is contained in the
                ' same DataGrid, uncomment the following line of code, which sets
                ' up a parent-child table relation. Then re-run the app and 
                ' expand the SupplierID node and view product details in the same
                ' DataGrid (the Product grid is not needed then). 

                'dsSupplierProducts.Relations.Add("Supplier_Products", _
                '    dsSupplierProducts.Tables("Supplier").Columns("SupplierID"), _
                '    dsSupplierProducts.Tables("Product").Columns("SupplierID"))

                ' Data has been successfully retrieved, so break out of the loop
                ' and close the status form.

                IsConnecting = False
                DidPreviouslyConnect = True

            Catch exp As Exception
                If strConn = SQL_CONNECTION_STRING Then
                    ' Couldn't connect to SQL Server.  Now try MSDE.
                    strConn = MSDE_CONNECTION_STRING
                    RaiseEvent ConnectionStatusChange("Connecting to MSDE")
                Else
                    ' Unable to connect to SQL Server or MSDE
                    RaiseEvent ConnectionFailure(CONNECTION_ERROR_MSG)
                End If
            End Try
        End While

        ' Reflect the success by raising the proper event.
        RaiseEvent ConnectionCompleted(True)

        ' Return the filled DataSet
        Return dsSupplierProducts

    End Function

    ' This subroutine takes a passed DataSet and updates the Northwind
    '   database, with the changes.
    Public Sub UpdateDataSet(ByVal inDS As DataSet)

        ' If the DataSet that was passed in is Nothing, exit this subrouting.
        If inDS Is Nothing Then
            Exit Sub
        End If

        Try
            ' First verify that the data adapters have been created, and call
            '   the CreateDataSet to build them if necessary.
            If (Me.sdaProducts Is Nothing Or Me.sdaSuppliers Is Nothing) Then
                CreateDataSet()
            End If

            ' Try to Update the DataSet. It is critical that everything is done
            '   in the proper sequence, unless you turn off the EnforceConstraints property.

            ' So there are two ways of updating the data. 
            ' The first is shutting off EnforceConstraints
            inDS.EnforceConstraints = False
            Me.sdaProducts.Update(inDS, "Product")
            Me.sdaSuppliers.Update(inDS, "Supplier")

            '' You can also do it in a proper sequence so as not to violate constraints at all.
            '' Products must be deleted first, so you don't strand products when a supplier
            ''   is deleted. Don't forget that you must check first to see if there are actual
            ''   changes to be made in each DataRowState, otherwise GetChanges will return
            ''   a Nothing DataSet and an exception will be raised.
            '' To use this method instead, simply uncomment the lines below.
            'If Not inDS.GetChanges(DataRowState.Deleted) Is Nothing Then
            '    Me.sdaProducts.Update(inDS.GetChanges(DataRowState.Deleted), "Products")
            '    Me.sdaSuppliers.Update(inDS.GetChanges(DataRowState.Deleted), "Suppliers")
            'End If

            'If Not inDS.GetChanges(DataRowState.Detached) Is Nothing Then
            '    Me.sdaProducts.Update(inDS.GetChanges(DataRowState.Detached), "Products")
            '    Me.sdaSuppliers.Update(inDS.GetChanges(DataRowState.Detached), "Suppliers")
            'End If

            '' Suppliers must be added first, so you don't create orphaned products
            'If Not inDS.GetChanges(DataRowState.Added) Is Nothing Then
            '    Me.sdaSuppliers.Update(inDS.GetChanges(DataRowState.Added), "Suppliers")
            '    Me.sdaProducts.Update(inDS.GetChanges(DataRowState.Added), "Products")
            'End If

            '' Changes can be made in any order
            'If Not inDS.GetChanges(DataRowState.Modified) Is Nothing Then
            '    Me.sdaSuppliers.Update(inDS.GetChanges(DataRowState.Modified), "Suppliers")
            '    Me.sdaProducts.Update(inDS.GetChanges(DataRowState.Modified), "Products")
            'End If

        Catch exc As Exception
            ' Alert the front end that an error occurred.
            RaiseEvent ConnectionFailure("Unable to update the data source.")
        End Try
    End Sub

End Class

